package user;
import java.sql.*;


public class Message {

	public static String RecordMessage(String name, String mail, String title, String content)
	{
		StringBuffer sBuf = new StringBuffer();
		if(name==null || name.equals("Your Name")) 
			name=""; 
		if(title==null || title.equals("Message Title")) 
			title=""; 
		if(content==null) 
			content=""; 
		if(mail==null || mail.equals("Your Email")) 
			mail=""; 
			
		if(name.length()==0) 
			sBuf.append("<script>alert(unescape('Please input your name!'))</script>"); 
		else if(title.length()==0) 
			sBuf.append("<script>alert(unescape('Please input the title!'))</script>"); 
		else if(content.length()==0) 
			sBuf.append("<script>alert(unescape('Please input your message!'))</script>"); 
		else 
		{ 
			java.util.Date date = new java.util.Date(); 
			String datetime = new Timestamp(date.getTime()).toString(); 
			
			try 
			{ 
				DBConnector db = DBConnector.getDBConnector();
				String InsertMessage = "insert into message values(null, '";			
				InsertMessage += title + "','" + name + "',";			 
				if(mail.length()==0) 
					InsertMessage += "null"; 
				else InsertMessage += "'" + mail + "'"; 
				InsertMessage += ",'" + datetime + "','" + content + "')";		

				try 
				{ 
					db.prepareStatement(InsertMessage);
					db.updatePstmt(); 
					sBuf.append("<script>alert(unescape('your message has been recorded. Thanks.'))</script>");
					
				} catch(Exception e) {
					System.out.println("Message.RecordMessage exception");
					// TODO Exception handling
				}
			}finally {				
					// TODO Final handling
			}				
			return sBuf.toString();
		} 
		return "";
	}
	public static String DeleteMessage(String DeleteMessageID)
	{
		StringBuffer sBuf = new StringBuffer();
		String DeleteMessageStatement = "delete from message where message_ID = " + DeleteMessageID;
		DBConnector db = DBConnector.getDBConnector();	
		db.prepareStatement(DeleteMessageStatement);
		db.updatePstmt();
		sBuf.append("<script>alert(unescape('The message has been deleted.'))</script>");
		return sBuf.toString();
	}
	public static String DisplayMessage(String message_search, String Order_By_SenderName, String Order_By_Topic, String Order_By_Email)
	{
		StringBuffer sBuf = new StringBuffer();
		DBConnector db = DBConnector.getDBConnector();	
		 
		try{
			db.prepareStatement("select count(*) from message");
			ResultSet result=db.queryPstmt(); 
			int message_count=0;
			if(result.next()) 
			{ 
				message_count=result.getInt(1); 
				result.close(); 
			} 
		
			if(message_count>0) 
			{ 	
				
				String queryMessages = "select * from message ";
	
				if (message_search != null && message_search.length() > 0)
					queryMessages += "where  (message_title like '%" + message_search
							+ "%' or message_name like '%" + message_search
							+ "%' or message_content like '%" + message_search
							+ "%' or message_mail like '%" + message_search + "%') ";	
				queryMessages += "order by null ";					
				if (Order_By_SenderName != null && Order_By_SenderName.length() > 0)
					queryMessages += ", message_name ";
				else if (Order_By_Topic != null && Order_By_Topic.length() > 0)
					queryMessages += ", message_title";
				else if (Order_By_Email != null && Order_By_Email.length() > 0)
					queryMessages += ", message_email ";
				else 
					queryMessages += ", message_time desc ";
				//out.println(queryMessages);				
				
					db.prepareStatement(queryMessages);
					result = db.queryPstmt();
					while(result.next()) 
					{ 
						String title=result.getString("message_title"); 
						String name=result.getString("message_name"); 
						String mail=result.getString("message_mail"); 
						Timestamp time=result.getTimestamp("message_time"); 
						Date date_time=new Date(time.getTime()); 
						Time time_time=new Time(time.getTime()); 
						String content=result.getString("message_content"); 
						
						sBuf.append("<tr><td width = 25%><font size=2>Sender:</font></td>");
						sBuf.append("<td width = 25%><font size=2>Topic:</font></td>");
						sBuf.append("<td width = 25%><font size=2>E-mail:</font></td>");
						sBuf.append("<td width = 25%><font size=2>Time:</font></td></tr>");
						sBuf.append("<tr bgcolor='#eeeeee'><td>" + name + "</td><td>" + title + "</td>");				
						sBuf.append("<td><a href=mailto:"+mail+">"+mail+"</a></td>"); 				
						sBuf.append("<td><font size=2>"+date_time+" "+time_time+"</font></td></tr>"); 
						sBuf.append("<tr><td colspan = 2><font size=2>Message:</font></td>");
						sBuf.append("<td align='right' colspan = 2>");				
						sBuf.append("("+message_count+")"); 
						sBuf.append("</td></tr>");
						sBuf.append("<tr bgcolor='#eeeeee'><td colspan=4>");
						sBuf.append("<table width = 80% align = center><tr><td>" + content + "</td></tr></table>");
						sBuf.append("</td></tr>");
						sBuf.append("<tr><td colspan = 4 align = center>");
						sBuf.append("<form action='administrator_account.jsp'>");
						sBuf.append(" <INPUT type=checkbox value=" + result.getString("message_ID") + " name=DeleteMessage>delete ");
						sBuf.append(" <INPUT type=submit value=apply name=SubmitDeleteMessage>");
						sBuf.append("</form></td></tr>");	
						message_count--; 
					} 			
			}
			else 
			{ 
				sBuf.append("<script>alert(unescape('There are no messages.'))</script>");			
			} 	
		}catch(Exception e) {
			System.out.println("Message.DisplayMessage exception");
		}
		return sBuf.toString();
	}
}
